using System;
using System.Data;
using System.Data.SqlClient;
using PHSRAG.Insight21.Context;

using PHSRAG.Insight21.CDS;
using PHSRAG.Insight21.BusinessObjects;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using GenerateKey = PHSRAG.Insight21.BusinessObjects.ModuleKey;
namespace PHSRAG.Insight21.ClinicalTrials
{
    /// <summary>
    /// The Budget class is the facade over the details for a single Budget.
    /// </summary>
    public class Budget : SaveableBusinessObject
    {
        #region Constants
        private static string BudgetKey = GenerateKey.MakeKey("Budget_D04A9763-6BA7-4ca3-983A-9718C3A56138", GenerateKey.ClinicalTrials);
        private const string TableName = "Budget";
        #endregion

        #region Instance Variables
        private BudgetStaffList budgetStaff;
        private BudgetTasks budgetTasks;
        private BudgetProcedures budgetProcedures;
        private BudgetVisits budgetVisits;
        private BudgetExpenses budgetExpenses;
        private BudgetExpenses budgetVariableCosts;
        private SqlCommand updateBudgetCommand;
        #endregion

        //	  cachePolicy = (Policy.ICachePolicy)Session[Policy.Names.CachePolicyKey];


        #region Public Properties
        /// <summary>
        /// Unique Id of the budget.  Returns 0 if this is a new budget.  Used to set various controls.
        /// </summary>
        public int Id
        {
            get
            {
                object o = DataContainer.Tables[TableName].Rows[0]["Id"];
                return (o == DBNull.Value) ? 0 : (int)o;
            }
        }

        /// <summary>
        /// Status code
        /// </summary>
        public string StatusCode
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["StatusCode"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["StatusCode"] = value;
            }
        }

        /// <summary>
        /// PersonId of the principal investigator for the clinical trial
        /// </summary>
        public int PIPersonId
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["PIPersonId"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["PIPersonId"] = value;
            }
        }


        /// <summary>
        /// access level to the budget
        /// </summary>
        public int FwkDomainUserId
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["FwkDomainUserId"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["FwkDomainUserId"] = value;
            }
        }

        /// <summary>
        /// PersonId of the originator of this budget
        /// </summary>
        public int OriginatorPersonId
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["OriginatorPersonId"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["OriginatorPersonId"] = value;
            }
        }

        /// <summary>
        /// Short title (nickname) of the clinical trial
        /// </summary>
        public string StudyShortTitle
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["StudyShortTitle"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["StudyShortTitle"] = value;
            }
        }

        /// <summary>
        /// Full title of the clinical trial
        /// </summary>
        public string StudyFullTitle
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["StudyFullTitle"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["StudyFullTitle"] = value;
            }
        }

        /// <summary>
        /// Sponsor Id
        /// </summary>
        public int SponsorId
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["SponsorId"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["SponsorId"] = value;
            }
        }

        /// <summary>
        /// Sponsor protocol number
        /// </summary>
        public string SponsorProtocolNumber
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["SponsorProtocolNumber"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["SponsorProtocolNumber"] = value;
            }
        }

        /// <summary>
        /// Study type code
        /// </summary>
        public string StudyType
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["StudyType"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["StudyType"] = value;
            }
        }

        /// <summary>
        /// Other study type text
        /// </summary>
        public string OtherType
        {
            get
            {
                object o = DataContainer.Tables[TableName].Rows[0]["OtherType"];
                return (o == DBNull.Value) ? "" : (string)o;
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["OtherType"] = value;
            }
        }

        /// <summary>
        /// Boolean which indicates whether the trial is initiated by the Principal Investigator
        /// </summary>
        public bool PIInitiated
        {
            get
            {
                return (bool)DataContainer.Tables[TableName].Rows[0]["PIInitiated"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["PIInitiated"] = value;
            }
        }

        /// <summary>
        /// Gets the overhead rate as a percentage.  Determined by checking overhead rate code 
        /// and other negotiated rate.
        /// </summary>
        public decimal OverheadRate
        {
            get
            {
                if (OverheadRateCode == ClinicalTrialsConstants.BudgetOverheadRateOther)
                {
                    return decimal.Parse(OtherNegotiatedRate) / 100;
                }
                else
                {
                    System.Web.HttpApplicationState app = System.Web.HttpContext.Current.Application;
                    ICachePolicy cachePolicy = (ICachePolicy)app[Policy.Names.ApplicationCachePolicyKey];
                    CDS.RefCodes refCodes = (RefCodes)cachePolicy.GetCachedObject(RefCodes.CacheKey);
                    RefCode refCode = refCodes.GetRefCode(OverheadRateCode);
                    return decimal.Parse(refCode.Attributes[ClinicalTrialsConstants.BudgetOverheadRateRate].ToString()) / 100;
                }
            }
        }

        /// <summary>
        /// Overhead rate code
        /// </summary>
        public string OverheadRateCode
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["OverheadRateCode"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["OverheadRateCode"] = value;
            }
        }

        /// <summary>
        /// Negotiated rate (if any)
        /// </summary>
        public string OtherNegotiatedRate
        {
            get
            {
                object o = DataContainer.Tables[TableName].Rows[0]["OtherNegotiatedRate"];
                return (o == DBNull.Value) ? null : o.ToString();
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["OtherNegotiatedRate"] = value;
            }
        }

        /// <summary>
        /// Number of visits
        /// </summary>
        public int SubjectVisits
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["SubjectVisits"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["SubjectVisits"] = value;
            }
        }

        /// <summary>
        /// Primary study location code
        /// </summary>
        public string PrimaryStudyLocationCode
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["PrimaryStudyLocationCode"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["PrimaryStudyLocationCode"] = value;
            }
        }

        /// <summary>
        /// Institution code.  Used to determine where which charge master feed to use.
        /// </summary>
        public string InstitutionCode
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["InstitutionCode"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["InstitutionCode"] = value;
            }
        }

        /// <summary>
        /// Number of subjects to be enrolled in the clinical trial
        /// </summary>
        public int EnrolledSubjects
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["EnrolledSubjects"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["EnrolledSubjects"] = value;
            }
        }

        /// <summary>
        /// Access level to the Budget
        /// </summary>
        public int AccessLevel
        {
            get
            {
                return (int)DataContainer.Tables[TableName].Rows[0]["AccessLevel"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["AccessLevel"] = value;
            }
        }

        /// <summary>
        /// IRB protocol number 
        /// </summary>
        public string IRBProtocolNumber
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["IRBProtocolNumber"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["IRBProtocolNumber"] = value;
            }
        }

        /// <summary>
        /// Folder number (formerly Rex Folder Number)
        /// </summary>
        public string FolderNumber
        {
            get
            {
                return (string)DataContainer.Tables[TableName].Rows[0]["FolderNumber"];
            }
            set
            {
                DataContainer.Tables[TableName].Rows[0]["FolderNumber"] = value;
            }
        }

        /// <summary>
        /// Gets the date and time this budget was created
        /// </summary>
        public DateTime InsertedDate
        {
            get
            {
                return (DateTime)DataContainer.Tables[TableName].Rows[0]["InsertedDate"];
            }
        }

        /// <summary>
        /// Gets the date and time this budget was last modified
        /// </summary>
        public DateTime LastModifiedDate
        {
            get
            {
                return (DateTime)DataContainer.Tables[TableName].Rows[0]["LastModifiedDate"];
            }
        }

        /// <summary>
        /// Gets the BudgetStaff associated with this budget
        /// </summary>
        public BudgetStaffList BudgetStaff
        {
            get
            {
                if (budgetStaff == null)
                {
                    budgetStaff = new BudgetStaffList(CachePolicy);
                    budgetStaff.Load(Utility.DataAccess.GetConnectionString(), Id);
                }
                return budgetStaff;
            }
        }

        /// <summary
        /// Gets the BudgetTasks associated with this budget
        /// </summary>
        public BudgetTasks BudgetTasks
        {
            get
            {
                if (budgetTasks == null)
                {
                    budgetTasks = new BudgetTasks(CachePolicy);
                    budgetTasks.Load(Utility.DataAccess.GetConnectionString(), Id);
                }
                return budgetTasks;
            }
        }

        /// <summary>
        /// Gets the BudgetProcedures associated with this budget
        /// </summary>
        public BudgetProcedures BudgetProcedures
        {
            get
            {
                if (budgetProcedures == null)
                {
                    budgetProcedures = new BudgetProcedures(CachePolicy);
                    budgetProcedures.Load(Utility.DataAccess.GetConnectionString(), Id);
                }
                return budgetProcedures;
            }
        }

        /// <summary>
        /// Gets the BudgetVisits associated with this budget
        /// </summary>
        public BudgetVisits BudgetVisits
        {
            get
            {
                if (budgetVisits == null)
                {
                    budgetVisits = new BudgetVisits(CachePolicy);
                    budgetVisits.Load(Utility.DataAccess.GetConnectionString(), Id);
                }
                return budgetVisits;
            }
        }

        /// <summary>
        /// Get the other expenses associated with this budget (not variable costs)
        /// </summary>
        public BudgetExpenses BudgetExpenses
        {
            get
            {
                if (budgetExpenses == null)
                {
                    budgetExpenses = new BudgetExpenses(CachePolicy);
                    budgetExpenses.Load(Utility.DataAccess.GetConnectionString(), Id, false);
                }
                return budgetExpenses;
            }
        }

        /// <summary>
        /// Gets the expenses indicated as Variable Costs associated with this budget.
        /// </summary>
        public BudgetExpenses BudgetVariableCosts
        {
            get
            {
                if (budgetVariableCosts == null)
                {
                    budgetVariableCosts = new BudgetExpenses(CachePolicy);
                    budgetVariableCosts.Load(Utility.DataAccess.GetConnectionString(), Id, true);
                }
                return budgetVariableCosts;
            }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Initialize the base class with the specified cache policy and create the data container.
        /// </summary>
        public Budget(ICachePolicy cachePolicy)
            : base(cachePolicy)
        {
            DataContainer = new DataSet();
            Utility.DataAccess.LoadDataSet(DataContainer, TableName,
                Utility.DataAccess.GetConnectionString(),
                "GetClinicalTrialBudget",
                Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, 0,
                "@domainUserId", SqlDbType.Int, 0));
            // Add empty row for setting properties if this is a new budget
            DataRow newRow = DataContainer.Tables[TableName].NewRow();
            DataContainer.Tables[TableName].Rows.Add(newRow);
        }
        #endregion

        #region Base Methods
        /// <summary>
        /// Cache this instance using the prevalent cache policy.
        /// </summary>
        public override void Cache()
        {
            CachePolicy.Cache(BudgetKey, this);
        }

        /// <summary>
        /// Remove this instance from the cache using the prevalent cache policy.
        /// </summary>
        public override void UnCache()
        {
            CachePolicy.UnCache(BudgetKey);
        }

        /// <summary>
        /// Load details pertaining to a specific budget ID.
        /// </summary>
        /// <param name="connectionString">Connection string to the database</param>
        /// <param name="args">Object array which must contain the budget ID</param>
        public override void Load(string connectionString, params object[] args)
        {
            if (args.Length == 0)
            {
                throw new Exception("Budget Load expects Budget ID.");
            }

            int budgetId = (int)args[0];
            int domainUserId = (int)args[1];

            try
            {
                DataContainer.Reset();
                Utility.DataAccess.LoadDataSet(DataContainer, TableName, Utility.DataAccess.GetConnectionString(),
                    "GetClinicalTrialBudget",
                    Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, budgetId,
                    "@domainUserId", SqlDbType.Int, domainUserId));
                if (DataContainer.Tables.Count == 0)
                    throw new Exception("Failed to load Budget information.");
            }
            catch (Exception e)
            {
                Utility.ExceptionReport.WriteToEventLog("Budget Load", e, true, Utility.ExceptionReport.ReportType.Error);
            }
        }
        /// <summary>
        /// Clones this budget which is selected by the user - this requires that we get each datatable of each kind of item to be cloned and then  
        /// </summary>
        /// <param name="budgetId">the budget id to be cloned</param>
        /// <returns>The new budget id that the setup page will load and display for the user to edit</returns>
        public static int CloneThisBudget(int budgetId)
        {
            // Clone the acutal clinical trial record 
            object o = Utility.DataAccess.ExecuteScalar(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudget", Utility.DataAccess.BuildSqlParameterArray(
                "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, 0));


            //Get all of the record datatables that we will be cloning
            DataTable TasksOld = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetTasks", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, budgetId));
            DataTable ProceduresOld = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetProcedures", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, budgetId));
            DataTable StaffOld = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetStaff", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, budgetId));
            DataTable VisitsOld = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetVisits", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, budgetId));

            //For each staff memeber we will clone each individual record and associate a budgetId with that
            for (int i = 0; i < StaffOld.Rows.Count; i++)
            {
                SqlParameter[] paramArrayStaff = Utility.DataAccess.BuildSqlParameterArray(
                    "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o), "@budgetStaffIdOld", SqlDbType.Int, StaffOld.Rows[i][0]);
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudgetStaff", paramArrayStaff);

            }


            SqlParameter[] paramArrayExpenses = Utility.DataAccess.BuildSqlParameterArray(
                    "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o));
            Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudgetExpense", paramArrayExpenses);



            //For each procedure we will clone each individual record and associate a budgetId with that
            for (int i = 0; i < ProceduresOld.Rows.Count; i++)
            {
                SqlParameter[] paramArrayProcedures = Utility.DataAccess.BuildSqlParameterArray(
                    "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o), "@budgetProcedureIdOld", SqlDbType.Int, ProceduresOld.Rows[i][0]);
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudgetProcedures", paramArrayProcedures);

            }
            //For each task we will clone each individual record and associate a budgetId with that
            for (int i = 0; i < TasksOld.Rows.Count; i++)
            {
                SqlParameter[] paramArrayTasks = Utility.DataAccess.BuildSqlParameterArray(
                    "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o), "@budgetTaskIdOld", SqlDbType.Int, TasksOld.Rows[i][0]);
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudgetTasks", paramArrayTasks);
            }
            DataTable Tasks = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetTasks", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, Convert.ToInt32(o)));

            //Here we make sure the correct staff memeber is attached to the correct task
            for (int i = 0; i < Tasks.Rows.Count; i++)
            {
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "UpdateClinicalTrialBudgetStaffIdForTasks", Utility.DataAccess.BuildSqlParameterArray("@OldStaffId", SqlDbType.Int, TasksOld.Rows[i][2], "@budgetTaskId", SqlDbType.Int, Tasks.Rows[i][0], "@budgetIdOld", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o)));
            }


            //For each procedure we will clone each individual record adn associate a budgetid with that
            for (int i = 0; i < VisitsOld.Rows.Count; i++)
            {
                SqlParameter[] paramArrayVisitsOld = Utility.DataAccess.BuildSqlParameterArray(
                    "@budgetId", SqlDbType.Int, budgetId, "@budgetIdNew", SqlDbType.Int, Convert.ToInt32(o), "@budgetIdVisitOld", SqlDbType.Int, VisitsOld.Rows[i][0]);
                Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "CloneClinicalTrialBudgetVisits", paramArrayVisitsOld);
            }

            DataTable VisitsNew = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetVisits", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, Convert.ToInt32(o)));
            Tasks = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetTasks", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, Convert.ToInt32(o)));
            for (int j = 0; j < Tasks.Rows.Count; j++)
            {
                ///For each visit we add in the correct task id for relating the two records to each other - otherwise the two will not be in sync. 
                for (int i = 0; i < VisitsOld.Rows.Count; i++)
                {
                    DataTable MileStoneVisit = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetMilestoneVisitsInformation", Utility.DataAccess.BuildSqlParameterArray("@ClinicalTrialBudgetVisitId", SqlDbType.Int, VisitsOld.Rows[i][0], "@relateId", SqlDbType.Int, TasksOld.Rows[j][0]));
                    SqlParameter[] paramArrayNewMilestoneVisits = Utility.DataAccess.BuildSqlParameterArray("@budgetMilestoneVisitId", SqlDbType.Int, 0, "@budgetVisitId", SqlDbType.Int, VisitsNew.Rows[i][0], "@relateClass", SqlDbType.VarChar, "BudgetTask", "@relateId", SqlDbType.Int, Tasks.Rows[j][0], "@required", SqlDbType.Int, MileStoneVisit.Rows[0][4], "@standardOfCare", SqlDbType.Int, MileStoneVisit.Rows[0][5]);
                    Utility.DataAccess.ExecuteScalar(Utility.DataAccess.GetConnectionString(), "UpdateClinicalTrialBudgetMilestoneVisits", paramArrayNewMilestoneVisits);

                }
            }

            DataTable Procedures = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetProcedures", Utility.DataAccess.BuildSqlParameterArray("@budgetId", SqlDbType.Int, Convert.ToInt32(o)));

            for (int j = 0; j < Procedures.Rows.Count; j++)
            {
                //For each visit we add in the correct procedure id for relating the two records to each other - otherwise the two will not be in sync. 
                for (int i = 0; i < VisitsOld.Rows.Count; i++)
                {
                    DataTable MileStoneVisit = Utility.DataAccess.GetDataTable(Utility.DataAccess.GetConnectionString(), "GetClinicalTrialBudgetMilestoneVisitsInformation", Utility.DataAccess.BuildSqlParameterArray("@ClinicalTrialBudgetVisitId", SqlDbType.Int, VisitsOld.Rows[i][0], "@relateId", SqlDbType.Int, ProceduresOld.Rows[j][0]));
                    SqlParameter[] paramArrayNewMilestoneVisits = Utility.DataAccess.BuildSqlParameterArray("@budgetMilestoneVisitId", SqlDbType.Int, 0, "@budgetVisitId", SqlDbType.Int, VisitsNew.Rows[i][0], "@relateClass", SqlDbType.VarChar, "BudgetProcedure", "@relateId", SqlDbType.Int, Procedures.Rows[j][0], "@required", SqlDbType.Int, MileStoneVisit.Rows[0][4], "@standardOfCare", SqlDbType.Int, MileStoneVisit.Rows[0][5]);
                    Utility.DataAccess.ExecuteNonQuery(Utility.DataAccess.GetConnectionString(), "UpdateClinicalTrialBudgetMilestoneVisits", paramArrayNewMilestoneVisits);
                }
            }
            return Convert.ToInt32(o);
        }


        /// <summary>
        /// Saves details associated with this budget.  Also creates ClinicalTrialBudgetVisit objects
        /// for each visit as specified in the number of visits property.
        /// </summary>
        /// <param name="connectionString">Connection string to the database</param>
        /// <param name="args">Optional parameters (ignored)</param>
        public override void Save(string connectionString, params object[] args)
        {
            SqlConnection connection = new SqlConnection(Utility.DataAccess.GetConnectionString());
            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                bool initialSave = (Id == 0);
                BuildCommand();
                updateBudgetCommand.Connection = connection;
                updateBudgetCommand.Transaction = transaction;

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.InsertCommand = updateBudgetCommand;
                adapter.UpdateCommand = updateBudgetCommand;
                adapter.Update(DataContainer, TableName);
                transaction.Commit();

                if (initialSave)
                {
                    SetupOtherItems();
                }

                // Create the (additional) budget visits and budget milestone visits for this budget.
                // These are required for the vists page and are associated with tasks and procedures.
                int numberOfVisits = BudgetVisits.Count;
                if (numberOfVisits < SubjectVisits)
                {
                    int newVisits = SubjectVisits - numberOfVisits;
                    for (int i = numberOfVisits + 1; i <= SubjectVisits; ++i)
                    {
                        BudgetVisit newVisit = BudgetVisits.CreateNewBudgetVisit();
                        newVisit.BudgetId = Id;
                        newVisit.VisitNumber = i;
                        // Default description, to be possibly changed later on the visits page
                        newVisit.VisitDescription = string.Format("Visit{0}", i);
                    }
                    BudgetVisits.Save(connectionString);

                    // Create the new budget milestone visits for any existing tasks
                    numberOfVisits = BudgetVisits.Count;
                    foreach (BudgetTask budgetTask in BudgetTasks)
                    {
                        for (int i = numberOfVisits - newVisits; i < numberOfVisits; ++i)
                        {
                            budgetTask.BudgetMilestoneVisits.CreateNewBudgetMilestoneVisit(BudgetVisits[i].Id, "BudgetTask");
                        }
                    }
                    budgetTasks.Save(connectionString);

                    // Create the new budget milestone visits for any existing procedures
                    foreach (BudgetProcedure budgetProcedure in BudgetProcedures)
                    {
                        for (int i = numberOfVisits - newVisits; i < numberOfVisits; ++i)
                        {
                            budgetProcedure.BudgetMilestoneVisits.CreateNewBudgetMilestoneVisit(BudgetVisits[i].Id, "BudgetProcedure");
                        }
                    }
                    budgetProcedures.Save(connectionString);
                }
                else if (numberOfVisits > SubjectVisits)
                {
                    for (int i = numberOfVisits - 1; i >= SubjectVisits; --i)
                    {
                        BudgetVisits.RemoveBudgetVisit(BudgetVisits[i]);
                    }
                    BudgetVisits.Save(connectionString);

                    // Remove the extra new budget milestone visits for any existing tasks
                    numberOfVisits = BudgetVisits.Count;
                    foreach (BudgetTask budgetTask in BudgetTasks)
                    {
                        for (int i = budgetTask.BudgetMilestoneVisits.Count - 1; i >= SubjectVisits; --i)
                        {
                            budgetTask.BudgetMilestoneVisits.RemoveBudgetMilestoneVisit(budgetTask.BudgetMilestoneVisits[i]);
                        }
                    }
                    budgetTasks.Save(connectionString);

                    // Remove the extra budget milestone visits for any existing procedures
                    foreach (BudgetProcedure budgetProcedure in BudgetProcedures)
                    {
                        for (int i = budgetProcedure.BudgetMilestoneVisits.Count - 1; i >= SubjectVisits; --i)
                        {
                            budgetProcedure.BudgetMilestoneVisits.RemoveBudgetMilestoneVisit(budgetProcedure.BudgetMilestoneVisits[i]);
                        }
                    }
                    budgetProcedures.Save(connectionString);
                }
            }
            catch (SqlException e)
            {
                transaction.Rollback();
                throw (new Exception(@"Error saving Budget to the database.", e));
            }
            catch (Exception e)
            {
                throw (new Exception(@"Error saving Budget to the database.", e));
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                transaction.Dispose();
            }
        }
        #endregion

        #region Private Methods
        private void BuildCommand()
        {
            if (updateBudgetCommand == null)
            {
                updateBudgetCommand = new SqlCommand();
                updateBudgetCommand.CommandText = "UpdateClinicalTrialBudget";
                updateBudgetCommand.CommandType = CommandType.StoredProcedure;
                updateBudgetCommand.CommandTimeout = Convert.ToInt32(Utility.Settings.GetKeyValue("Database.Insight.CommandTimeout", "0"));
                SqlParameter paramId = new SqlParameter("@budgetId", SqlDbType.Int);
                paramId.SourceColumn = "Id";
                paramId.Direction = ParameterDirection.InputOutput;
                updateBudgetCommand.Parameters.Add(paramId);
                updateBudgetCommand.Parameters.Add("@statusCode", SqlDbType.VarChar).SourceColumn = "StatusCode";
                updateBudgetCommand.Parameters.Add("@PIPersonId", SqlDbType.Int).SourceColumn = "PIPersonId";
                updateBudgetCommand.Parameters.Add("@originatorPersonId", SqlDbType.Int).SourceColumn = "OriginatorPersonId";
                updateBudgetCommand.Parameters.Add("@studyShortTitle", SqlDbType.VarChar).SourceColumn = "StudyShortTitle";
                updateBudgetCommand.Parameters.Add("@studyFullTitle", SqlDbType.VarChar).SourceColumn = "StudyFullTitle";
                updateBudgetCommand.Parameters.Add("@sponsorId", SqlDbType.Int).SourceColumn = "SponsorId";
                updateBudgetCommand.Parameters.Add("@sponsorProtocolNumber", SqlDbType.VarChar).SourceColumn = "SponsorProtocolNumber";
                updateBudgetCommand.Parameters.Add("@studyType", SqlDbType.VarChar).SourceColumn = "StudyType";
                updateBudgetCommand.Parameters.Add("@otherType", SqlDbType.VarChar).SourceColumn = "OtherType";
                updateBudgetCommand.Parameters.Add("@PIInitiated", SqlDbType.Bit).SourceColumn = "PIInitiated";
                updateBudgetCommand.Parameters.Add("@overheadRateCode", SqlDbType.VarChar).SourceColumn = "OverheadRateCode";
                updateBudgetCommand.Parameters.Add("@otherNegotiatedRate", SqlDbType.Decimal).SourceColumn = "OtherNegotiatedRate";
                updateBudgetCommand.Parameters.Add("@subjectVisits", SqlDbType.Int).SourceColumn = "SubjectVisits";
                updateBudgetCommand.Parameters.Add("@primaryStudyLocationCode", SqlDbType.VarChar).SourceColumn = "PrimaryStudyLocationCode";
                updateBudgetCommand.Parameters.Add("@institutionCode", SqlDbType.VarChar).SourceColumn = "InstitutionCode";
                updateBudgetCommand.Parameters.Add("@enrolledSubjects", SqlDbType.Int).SourceColumn = "EnrolledSubjects";
                updateBudgetCommand.Parameters.Add("@IRBProtocolNumber", SqlDbType.VarChar).SourceColumn = "IRBProtocolNumber";
                updateBudgetCommand.Parameters.Add("@folderNumber", SqlDbType.VarChar).SourceColumn = "FolderNumber";
                SqlParameter insertedDate = new SqlParameter("@insertedDate", SqlDbType.DateTime);
                insertedDate.SourceColumn = "InsertedDate";
                insertedDate.Direction = ParameterDirection.Output;
                updateBudgetCommand.Parameters.Add(insertedDate);
                SqlParameter lastModifiedDate = new SqlParameter("@lastModifiedDate", SqlDbType.DateTime);
                lastModifiedDate.SourceColumn = "LastModifiedDate";
                lastModifiedDate.Direction = ParameterDirection.Output;
                updateBudgetCommand.Parameters.Add(lastModifiedDate);
            }
        }

        private void SetupOtherItems()
        {
            // Setup staff
            System.Web.HttpApplicationState app = System.Web.HttpContext.Current.Application;
            ICachePolicy cachePolicy = (ICachePolicy)app[Policy.Names.ApplicationCachePolicyKey];
            CDS.RefCodes refCodes = (RefCodes)cachePolicy.GetCachedObject(RefCodes.CacheKey);

            BudgetStaff newBudgetStaff = BudgetStaff.CreateNewBudgetStaff();
            newBudgetStaff.StudyStaffRoleCode = "ClinicalTrialBudgetStaffRoleNA";
            RefCode refCode = refCodes.GetRefCode(newBudgetStaff.StudyStaffRoleCode);
            newBudgetStaff.Rate = refCode.Attributes["Rate"].ToString();
            newBudgetStaff.StudyStaffRoleMeaning = refCode.Meaning;

            newBudgetStaff = BudgetStaff.CreateNewBudgetStaff();
            newBudgetStaff.StudyStaffRoleCode = "ClinicalTrialBudgetStaffRolePI";
            refCode = refCodes.GetRefCode(newBudgetStaff.StudyStaffRoleCode);
            newBudgetStaff.Rate = refCode.Attributes["Rate"].ToString();
            newBudgetStaff.StudyStaffRoleMeaning = refCode.Meaning;

            newBudgetStaff = BudgetStaff.CreateNewBudgetStaff();
            newBudgetStaff.StudyStaffRoleCode = "ClinicalTrialBudgetStaffRoleRNCRC";
            refCode = refCodes.GetRefCode(newBudgetStaff.StudyStaffRoleCode);
            newBudgetStaff.Rate = refCode.Attributes["Rate"].ToString();
            newBudgetStaff.StudyStaffRoleMeaning = refCode.Meaning;

            newBudgetStaff = BudgetStaff.CreateNewBudgetStaff();
            newBudgetStaff.StudyStaffRoleCode = "ClinicalTrialBudgetStaffRoleCRC";
            refCode = refCodes.GetRefCode(newBudgetStaff.StudyStaffRoleCode);
            newBudgetStaff.Rate = refCode.Attributes["Rate"].ToString();
            newBudgetStaff.StudyStaffRoleMeaning = refCode.Meaning;
            BudgetStaff.Save(Utility.DataAccess.GetConnectionString());

            // Setup tasks
            BudgetStaff staffPI = BudgetStaff.GetStaffByRoleCode("ClinicalTrialBudgetStaffRolePI");
            BudgetStaff staffCRC = BudgetStaff.GetStaffByRoleCode("ClinicalTrialBudgetStaffRoleCRC");
            BudgetTask newBudgetTask = BudgetTasks.CreateNewBudgetTask();
            newBudgetTask.BudgetTaskCode = "ClinicalTrialBudgetTask01";
            refCode = refCodes.GetRefCode(newBudgetTask.BudgetTaskCode);
            newBudgetTask.Hours = refCode.Attributes["Hours"].ToString();
            newBudgetTask.BudgetStaffId = staffPI.Id;
            newBudgetTask.TaskCost = decimal.Multiply(decimal.Parse(staffPI.Rate), decimal.Parse(newBudgetTask.Hours)).ToString("N2");

            newBudgetTask = BudgetTasks.CreateNewBudgetTask();
            newBudgetTask.BudgetTaskCode = "ClinicalTrialBudgetTask04";
            refCode = refCodes.GetRefCode(newBudgetTask.BudgetTaskCode);
            newBudgetTask.Hours = refCode.Attributes["Hours"].ToString();
            newBudgetTask.BudgetStaffId = staffPI.Id;
            newBudgetTask.TaskCost = decimal.Multiply(decimal.Parse(staffPI.Rate), decimal.Parse(newBudgetTask.Hours)).ToString("N2");

            newBudgetTask = BudgetTasks.CreateNewBudgetTask();
            newBudgetTask.BudgetTaskCode = "ClinicalTrialBudgetTask07";
            refCode = refCodes.GetRefCode(newBudgetTask.BudgetTaskCode);
            newBudgetTask.Hours = refCode.Attributes["Hours"].ToString();
            newBudgetTask.BudgetStaffId = staffCRC.Id;
            newBudgetTask.TaskCost = decimal.Multiply(decimal.Parse(staffCRC.Rate), decimal.Parse(newBudgetTask.Hours)).ToString("N2");

            newBudgetTask = BudgetTasks.CreateNewBudgetTask();
            newBudgetTask.BudgetTaskCode = "ClinicalTrialBudgetTask09";
            refCode = refCodes.GetRefCode(newBudgetTask.BudgetTaskCode);
            newBudgetTask.Hours = refCode.Attributes["Hours"].ToString();
            newBudgetTask.BudgetStaffId = staffCRC.Id;
            newBudgetTask.TaskCost = decimal.Multiply(decimal.Parse(staffCRC.Rate), decimal.Parse(newBudgetTask.Hours)).ToString("N2");
            BudgetTasks.Save(Utility.DataAccess.GetConnectionString());

            // Setup Expenses
            BudgetExpense newBudgetExpense = BudgetExpenses.CreateNewBudgetExpense(false);
            newBudgetExpense.ApplyOverhead = false;
            newBudgetExpense.StudyCostCode = "ClinicalTrialBudgetExpenseIRBReview";
            refCode = refCodes.GetRefCode(newBudgetExpense.StudyCostCode);
            newBudgetExpense.ExpenseCost = refCode.Attributes["Rate"].ToString();

            newBudgetExpense = BudgetExpenses.CreateNewBudgetExpense(false);
            newBudgetExpense.StudyCostCode = "ClinicalTrialBudgetExpenseIRBSubmissionPrepAdult";
            refCode = refCodes.GetRefCode(newBudgetExpense.StudyCostCode);
            newBudgetExpense.ExpenseCost = refCode.Attributes["Rate"].ToString();

            newBudgetExpense = BudgetExpenses.CreateNewBudgetExpense(false);
            newBudgetExpense.StudyCostCode = "ClinicalTrialBudgetExpenseArchiving";
            refCode = refCodes.GetRefCode(newBudgetExpense.StudyCostCode);
            newBudgetExpense.ExpenseCost = refCode.Attributes["Rate"].ToString();
            BudgetExpenses.Save(Utility.DataAccess.GetConnectionString());

            // Setup Variable Costs
            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "Protocol Amendment Submission";
            newBudgetExpense.ExpenseCost = "600.00";
            newBudgetExpense.ApplyOverhead = true;

            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "PI Safety Report Submission";
            newBudgetExpense.ExpenseCost = "200.00";
            newBudgetExpense.ApplyOverhead = true;

            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "SAE Submission";
            newBudgetExpense.ExpenseCost = "200.00";
            newBudgetExpense.ApplyOverhead = true;

            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "Advertising / Recruitment";
            newBudgetExpense.ExpenseCost = "0.00";
            newBudgetExpense.ApplyOverhead = true;

            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "Coordinator meetings with monitor";
            newBudgetExpense.ExpenseCost = "0.00";
            newBudgetExpense.ApplyOverhead = true;

            newBudgetExpense = BudgetVariableCosts.CreateNewBudgetExpense(true);
            newBudgetExpense.StudyCostCode = "Study close out meeting with monitor";
            newBudgetExpense.ExpenseCost = "0.00";
            newBudgetExpense.ApplyOverhead = true;
            BudgetVariableCosts.Save(Utility.DataAccess.GetConnectionString());

        }
        #endregion

        #region Static Methods
        /// <summary>
        /// Returns the CacheKey for the budget class
        /// </summary>
        public static string CacheKey
        {
            get
            {
                return BudgetKey;
            }
        }
        #endregion

    }
}